﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Eacademy.Domain.Abstractions;
using Eacademy.Domain.Entities;
using Eacademy.Domain.Models;
using Eacademy.Library.Helpers;
using Elmah;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Transactions;
using SubSonic.DataProviders;

namespace Eacademy.Domain.Repository
{
    public class AssignmentRepository: IAssignmentRepository
    {
        EacademyDB db = new EacademyDB();
        public List<AdminAssignmentsViewModel> GetAssignmentsBySubjectId(int subjectId, int mentorUserId, int pageIndex, int pageSize, out int totalCount, bool count = true)
        {
            totalCount = 0;
            DbFactory factory = new DbFactory();
            Database db = factory.database;
            List<AdminAssignmentsViewModel> assignmentViewList = new List<AdminAssignmentsViewModel>();
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.AddStartRemove();
                sql.AppendLine(@"SELECT a.*,asl.subject_id");
                sql.AddEndRemove();
                sql.AppendLine(@"FROM assignments_subjects asl");
                sql.AppendLine(@"LEFT JOIN assignments a ON asl.assignment_id = a.assignment_id ");
                sql.AppendLine(" WHERE 1 = 1 ");
                sql.AppendLine(" AND asl.subject_id = ?SubjectId ");
                sql.AppendLine(" AND asl.mentor_id = ?MentorUserId");

                if (pageSize > 0)
                {
                    //control page size
                    pageSize = pageSize > 10 ? 10 : pageSize;

                    sql.AddStartRemove();
                    sql.Append(" LIMIT ?StartIndex, ?MaxRecord ");
                    sql.AddEndRemove();
                }

                DbCommand comm = db.GetSqlStringCommand(sql.ToString());
                db.AddInParameter(comm, "?SubjectId", DbType.Int32, subjectId);
                db.AddInParameter(comm, "?StartIndex", DbType.Int32, (pageIndex - 1) * pageSize);
                db.AddInParameter(comm, "?MaxRecord", DbType.Int32, pageSize);
                db.AddInParameter(comm, "?MentorUserId", DbType.Int32, mentorUserId);
                DataSet ds = factory.ExecuteQuery(comm);

                DataTable studentsTable = ds.Tables[0];


                assignmentViewList = studentsTable.AsEnumerable().Select(datarow => new AdminAssignmentsViewModel
                {
                    Assignment = datarow["assignment"].ToString(),
                    AssignmentId = datarow["assignment_id"].ToInteger(),
                    SubjectId = datarow["subject_id"].ToInteger(),
                    Title = datarow["title"].ToString()
                }).ToList();

                if (count)
                {
                    string countQuery = sql.ToString().GenerateCountQuery();
                    comm.CommandText = countQuery;
                    ds = factory.ExecuteQuery(comm);
                    totalCount = ds.Tables[0].Rows[0]["count"].ToInteger();
                }
                

                return assignmentViewList;
            }
            catch (Exception ex)
            {
                
                
            }
            return assignmentViewList;
        }



        public AdminAssignmentsViewModel GetAssignmentByAssignmentId(int id)
        {
            AdminAssignmentsViewModel adminAssignmentViewModel = new AdminAssignmentsViewModel();
            try
            {
                Assignment assign = db.Select.From<Assignment>().Where(AssignmentsTable.AssignmentIdColumn)
                    .IsEqualTo(id).ExecuteSingle<Assignment>();

                adminAssignmentViewModel.Title = assign.Title;
                adminAssignmentViewModel.Assignment = assign.Assignment;
                adminAssignmentViewModel.AssignmentId = id;
                adminAssignmentViewModel.Files = assign.Files;
                adminAssignmentViewModel.DateDue = assign.DateDue.ToString("MMMM dd, yyyy");

                return adminAssignmentViewModel;
            }
            catch (Exception ex)
            {
                ErrorSignal.FromCurrentContext().Raise(ex);
            }
            return adminAssignmentViewModel;
        }

        public bool DeleteAssignment(int lessonId)
        {
            throw new NotImplementedException();
        }

        public bool UpdateAssignment(AdminAssignmentsViewModel adminAssignmentsViewModel)
        {
            throw new NotImplementedException();
        }

        public bool AddAssignment(AdminAssignmentsViewModel adminAssignmentsViewModel, int subjectId, int mentorUserId)
        {
            try
            {

                using (TransactionScope ts = new TransactionScope())
                {
                    using (SharedDbConnectionScope scs = new SharedDbConnectionScope())
                    {
                        Mentor mentor = db.Select.From<Mentor>()
                            .Where(MentorsTable.UserIdColumn).IsEqualTo(mentorUserId)
                            .ExecuteSingle<Mentor>();

                        Assignment assign = new Assignment();
                        assign.Title = adminAssignmentsViewModel.Title;
                        assign.Assignment = HttpUtility.HtmlDecode(adminAssignmentsViewModel.Assignment);
                        assign.DatePosted = DateTime.Now;
                        assign.DateDue = adminAssignmentsViewModel.DateDue.ToDate();
                        assign.Save();

                        AssignmentsSubjects assignSubject = new AssignmentsSubjects();
                        assignSubject.AssignmentId = assign.AssignmentId;
                        assignSubject.SubjectId = subjectId;
                        assignSubject.MentorId = mentorUserId;
                        assignSubject.Save();
                    }
                    ts.Complete();
                }

                return true;
            }
            catch (Exception ex)
            {
            }
            return false;
        }
    }
}